package com.ruoyi.tongji.run;

import com.ruoyi.tongji.entity.Staff;
import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.DateUtil;
import com.ruoyi.tongji.utils.DateUtils;
import com.ruoyi.tongji.utils.JxlsUtils;
import com.ruoyi.tongji.utils.PlatformException;
import org.apache.commons.lang3.StringUtils;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 职工任务数统计
 */
public class 每月员工统计 {


    public static void main(String[] args) {
        Date dateRangeStart = DateUtil.getDate("2024-09-01 0000000","yyyy-MM-dd HHmmsss");//把这个变量的日期改成统计开始日期
        Date dateRangeEnd = DateUtil.getDate("2024-09-10 2359000","yyyy-MM-dd HHmmsss");//把这个变量的日期改成统计截止日期
        每月员工统计.start(dateRangeStart,dateRangeEnd);
    }

    public static File start(Date dateRangeStart, Date dateRangeEnd) {
        String month = DateUtils.getMonth(dateRangeStart);//以开始时间所在月份作为月份
        String root = "E:\\文档\\2024\\每月统计\\" + month + "月\\程序查询";

        SQLManager jiangzhanSqlManager = ManagerFactory.jiangzhanSqlManager();
        List<Staff> staffList = jiangzhanSqlManager.execute(new SQLReady("select * from member", new Object[] {}), Staff.class);

        Map model = new HashMap();
        model.put("staffList", staffList);
        for (Iterator<Staff> iterator = staffList.iterator(); iterator.hasNext(); ) {
            Staff staff =  iterator.next();
            getStaff(staff, dateRangeStart, dateRangeEnd);

            if (StringUtils.equals(staff.getName().toString(), "焦连云")) {
                staff.setName("焦连芸");
            }
        }

        String excelTemplate = "excelTemplates/员工/员工.xlsx";
        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
            if(is==null) {
                throw new PlatformException("模板资源不存在："+excelTemplate);
            }
            File f = new File(root + "\\员工" +  month + "月.xlsx");
            f.getParentFile().mkdirs();
            FileOutputStream os = new FileOutputStream(f);
            JxlsUtils.exportExcel(is,os,model);

            return f;
        } catch (IOException e) {
            throw new PlatformException(e.getMessage());
        }
    }


    public static Map getStaff(Staff staff, Date dateRangeStart, Date dateRangeEnd) {
        String dateStringRangeStart = DateUtil.format(dateRangeStart, "yyyyMMdd0000000");
        String dateStringRangeEnd = DateUtil.format(dateRangeEnd, "yyyyMMdd2300000");

        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        //查询时间范围内员工名下浆员的采浆袋数
        //List<HashMap> staffDaishu = sqlManager.execute(new SQLReady("select count(*) as 袋数, donor.donorId as 浆员编号 , donor.donorName as 浆员姓名, donor.remark as 备注 from t_bims_expense_payment as pay left join t_bims_code_area area on pay.codeArea = area.ccode left join t_bims_donor donor on pay.donorId = donor.donorId where pay.codePremium = 'TRANSPORTEXPENSE' and pay.collectDate between ? and ? and donor.issuingDate between ? and ? and donor.remark like ?", new Object[] {dateStringRangeStart,dateStringRangeEnd,dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        //查询时间范围内员工名下浆员的采浆袋数
        List<HashMap> staffDaishu = taibangSqlManager.execute(new SQLReady("select count(*) as 袋数, donor.donorId as 浆员编号 , donor.donorName as 浆员姓名, donor.remark as 备注 from t_bims_expense_payment as pay left join t_bims_code_area area on pay.codeArea = area.ccode left join t_bims_donor donor on pay.donorId = donor.donorId where pay.codePremium = 'TRANSPORTEXPENSE' and pay.collectDate between ? and ? and donor.remark like ?", new Object[] {dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long daishu = (Long)staffDaishu.get(0).get("袋数");
        staff.setDaishu(daishu.intValue());

        List<HashMap> staffRenshu = taibangSqlManager.execute(new SQLReady("select count(*) as 人数 from (select count(*) as 袋数, donor.donorId as 浆员编号 , donor.donorName as 浆员姓名, donor.remark as 备注 from t_bims_expense_payment as pay left join t_bims_code_area area on pay.codeArea = area.ccode left join t_bims_donor donor on pay.donorId = donor.donorId where pay.codePremium = 'TRANSPORTEXPENSE' and pay.collectDate between ? and ? and donor.remark like ? group by donor.donorId) as a", new Object[] {dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long renshu = (Long)staffRenshu.get(0).get("人数");
        staff.set("renshu",renshu.intValue());

        //查询时间范围内员工名下新浆员人数
        List<HashMap> xinCountList = taibangSqlManager.execute(new SQLReady("select count(*) as 人数 from t_bims_donor as donor where donor.firstCollectDate between ? and ? and donor.remark like ?", new Object[] {dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long xinCount = (Long)xinCountList.get(0).get("人数");
        staff.setXinCount(xinCount.intValue());

        //查询时间范围内员工名下超半年浆员人数
        List<HashMap> chaoCountList = taibangSqlManager.execute(new SQLReady("select count(*) as 人数 from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(curColDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(collectionDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect \n" +
                "     donorName,donorId as aa,curColDate,\n" +
                "\t\t (select c.beginDate from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate < curColDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as collectionDate\n" +
                "\tfrom (\n" +
                "\t   select donor.donorName as donorName,donor.donorId as donorId, currentCollect.beginDate as curColDate from t_bims_plasma_collection as currentCollect left join t_bims_donor as donor on currentCollect.donorId = donor.donorId where currentCollect.collectionNumberFlag = 'Y' and " +
                " currentCollect.beginDate between ? and ? and donor.remark like ? \n" +
                "\t) as a\n" +
                ") as b) as c where c.days > (180)", new Object[] {dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long chaoCount = (Long)chaoCountList.get(0).get("人数");
        staff.setChaoCount(chaoCount.intValue());

        //查询时间范围内员工名下检疫期浆员采浆人数
        List<HashMap> jianXianList = taibangSqlManager.execute(new SQLReady("select count(*) as 人数 from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(curColDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(collectionDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect \n" +
                "     donorName,donorId as aa,curColDate,\n" +
                "\t\t (select c.beginDate from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate < curColDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as collectionDate\n" +
                "\tfrom (\n" +
                "\t   select donor.donorName as donorName,donor.donorId as donorId, currentCollect.beginDate as curColDate from t_bims_plasma_collection as currentCollect left join t_bims_donor as donor on currentCollect.donorId = donor.donorId where currentCollect.collectionNumberFlag = 'Y' \n" +
                "     and currentCollect.beginDate between ? and ? and donor.remark like ? \n" +
                "\t) as a\n" +
                ") as b) as c where c.days > (90) and c.days <= 180", new Object[] {dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long jianXianCount = (Long)jianXianList.get(0).get("人数");
        staff.setJianXianCount(jianXianCount.intValue());

        return null;
    }
}
